﻿Imports System.Data.SqlClient

Module mssql_query
    Public najnowszeHasloUzytkownika As String = "SELECT TOP 1 [users].id, pass, data " + _
                    "FROM [users], users_pass " + _
                    "WHERE " + _
                        "[users].id=users_pass.users_id " + _
                    "AND login=@login " + _
                    "ORDER BY pass DESC;"

    Public Function sql_ileWynikow(ByVal sqlc As SqlCommand) As Integer
        Dim sqlcommand As SqlCommand = sqlc
        Dim reader As SqlDataReader
        Dim licznik As Integer = 0

        sqlcommand.CommandType = CommandType.Text
        sqlcommand.Connection = New SqlConnection(CONN_String)

        Try
            sqlcommand.Connection.Open()
            reader = sqlcommand.ExecuteReader()
            reader.Read()

            Try
                licznik = reader(0)
            Catch ex As Exception
                licznik = 0
            End Try
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            sqlcommand.Connection.Close()
        End Try

        Return licznik
    End Function

    ' NFO: uniwersalne zapytanie zwraca dane typu integer
    Public Function sql_zwrot_zapytania_int(ByVal query As String) As Integer
        Dim wynik As Integer = 0

        Dim sqlcommand As New SqlCommand
        sqlcommand.CommandText = query
        sqlcommand.CommandType = CommandType.Text
        sqlcommand.Connection = New SqlConnection(CONN_String)
        sqlcommand.Connection.Open()

        Dim reader As SqlDataReader
        reader = sqlcommand.ExecuteReader()
        reader.Read()

        If (IsDBNull(reader(0))) Then
            wynik = 0
        Else
            wynik = reader(0)
        End If

        sqlcommand.Connection.Close()

        Return wynik
    End Function

    ' NFO: uniwersalne zapytanie zwraca dane typu string
    Public Function sql_zwrot_zapytania_string(ByVal query As String) As String
        Dim wynik As String = ""

        Dim sqlcommand As New SqlCommand
        sqlcommand.CommandText = query
        sqlcommand.CommandType = CommandType.Text
        sqlcommand.Connection = New SqlConnection(CONN_String)
        sqlcommand.Connection.Open()

        Dim reader As SqlDataReader

        reader = sqlcommand.ExecuteReader()
        reader.Read()

        Try
            wynik = reader(0)
        Catch ex As Exception
            wynik = ""
        Finally
            sqlcommand.Connection.Close()
        End Try

        Return wynik
    End Function

    Public Function zapis_do_logu(ByVal kod As String, ByVal id_user As Integer, ByVal dod_info As String)
        Dim SQLCCommand As New SqlCommand
        Dim queryINSERT As String = "INSERT INTO [log]" + _
                                       "([kod]" + _
                                       ",[users_id]" + _
                                       ",[dod_info]" + _
                                       ",[data])" + _
                                    "VALUES" + _
                                       "(@kod" + _
                                       ",@users_id" + _
                                       ",@dod_info" + _
                                       ",GETDATE())"

        'MsgBox(queryINSERT + "kod:" + kod.ToString + " id_user" + id_user.ToString + " dod: " + dod_info)

        SQLCCommand.Parameters.Add("@kod", SqlDbType.VarChar).Value = kod
        SQLCCommand.Parameters.Add("@users_id", SqlDbType.Int).Value = id_user
        SQLCCommand.Parameters.Add("@dod_info", SqlDbType.VarChar).Value = dod_info

        SQLCCommand.CommandText = queryINSERT
        SQLCCommand.CommandType = CommandType.Text
        SQLCCommand.Connection = New SqlConnection(CONN_String)

        Try
            SQLCCommand.Connection.Open()
            SQLCCommand.ExecuteNonQuery()
            SQLCCommand.Connection.Close()
        Catch ex As Exception
            MsgBox("Błąd autoryzacji\konfiguracji")
        End Try

        Return True
    End Function

    Public Function hashPass(ByVal passToHass As String)
        Dim hash As String = ""

        Dim SQLCCommand As New SqlCommand
        Dim reader As SqlDataReader

        Dim querySELECT As String = "SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA1', @pass)), 3, 50)"

        SQLCCommand.Parameters.Add("@pass", SqlDbType.VarChar).Value = passToHass

        SQLCCommand.CommandText = querySELECT
        SQLCCommand.CommandType = CommandType.Text
        SQLCCommand.Connection = New SqlConnection(CONN_String)

        SQLCCommand.Connection.Open()
        reader = SQLCCommand.ExecuteReader()
        reader.Read()
        hash = Convert.ToString(reader(0))
        SQLCCommand.Connection.Close()

        Return hash
    End Function
End Module
